package cs2340.droidceptiontodo;

import java.util.ArrayList;

/**
 * This is the wonderful awesome database class. We're so cool :)
 * 
 * purpose: to store useraccounts and items in tables
 */
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * 
 * Database class: handles all data
 * 
 * @author Droidception
 *
 */
public class Database {
	//Create a static instance of the Database object
	private static Database instance = null;
	//the current user
	public static UserAccount currentUser = null;
	
	//the columns (instance variables) for the Users
	public static final String KEY_ROWID = "_id";
	public static final String KEY_NAME = "user_name";
	public static final String KEY_LOGIN = "user_login";
	public static final String KEY_EMAIL = "user_email";
	public static final String KEY_PASSWORD = "user_password";

	//the columns (instance variables) for the to do items
	public static final String KEY_ITEMNAME = "item_name";
	public static final String KEY_USER = "item_user";
	public static final String KEY_DETAILS = "item_details";
	public static final String KEY_CATEGORY = "item_category";
	public static final String KEY_DUEDATE = "item_duedate";
	public static final String KEY_DUETIME = "item_duetime";
	public static final String KEY_LOCATION = "item_location";
	public static final String KEY_ISCHECKED = "item_ischecked";
	
	//the columns for settings
	public static final String KEY_SETTING = "setting";
	public static final String KEY_VALUE = "value";
	
	//the different tables, one for users, one for to-do-item
	private static final String DATABASE_NAME = "ToDoDB";
	private static final String DATABASE_USERTABLE = "userTable";
	private static final String DATABASE_TODOITEMTABLE = "todoitemTable";
	private static final String DATABASE_SETTINGS = "settings";
	private static final int DATABASE_VERSION = 3;
	
	//the new boston tutorials told me to put these here. you don't need to pay attention to them
	private DbHelper dbHelper; 
	private final Context ourContext;
	private SQLiteDatabase database;
	
	//this private class creates the database for the first time and accesses it if it already exists...
	//basically, this is how databases have persistence
	/**
	 * private DbHelper class
	 * responsible for database creation/access
	 * 
	 * @author Sheena
	 *
	 */
	private static class DbHelper extends SQLiteOpenHelper{
		/**
		 * feeds parameters to SQLiteOpenHelper constructor
		 * @param context
		 */
		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/**
		 * creates a table of users
		 * creates a table of to do items
		 * ...these are in SQL. SPACES MATTER
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			//creates the table of users
			db.execSQL("CREATE TABLE " + DATABASE_USERTABLE + " (" + 
					KEY_NAME + " TEXT NOT NULL, " + 
					KEY_LOGIN + " TEXT NOT NULL, " +
					KEY_EMAIL + " TEXT NOT NULL, " + 
					KEY_PASSWORD + " TEXT NOT NULL);"
					);
			//creates the table of to do items
			db.execSQL("CREATE TABLE " + DATABASE_TODOITEMTABLE + " (" +
					KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
					KEY_ITEMNAME + " TEXT NOT NULL, " + 
					KEY_USER + " TEXT NOT NULL, " +
					KEY_DETAILS + " TEXT NOT NULL, " +
					KEY_CATEGORY + " TEXT NOT NULL, " + 
					KEY_DUEDATE + " TEXT NOT NULL, " + 
					KEY_DUETIME + " TEXT NOT NULL, " + 
					KEY_LOCATION + " TEXT NOT NULL, " + 
					KEY_ISCHECKED + " TEXT NOT NULL);"
					);
			//table of....
			db.execSQL("CREATE TABLE " + DATABASE_SETTINGS + " (" +
					KEY_SETTING + " TEXT NOT NULL, " +
					KEY_VALUE + " TEXT NOT NULL);"
					);
			Log.d("DEBUG", "Database created.");
		}

		/**
		 * if the database tables already exist, instead of making 
		 * new ones, just pull the old ones up
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_USERTABLE);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TODOITEMTABLE);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_SETTINGS);
			onCreate(db);
		}
	}
	/**
	 * creates a database
	 * @param c
	 */
	public Database(Context c){
		ourContext = c;
	}
	
	/**
	 * Getter for the static Database instance as Singleton
	 * @param c
	 * @return
	 */
	public static Database getInstance(Context c){
		if (instance == null){
			instance = new Database(c);
		}
		return instance;
	}
	
	/**
	 * opens the database and uses the database helper to get a writeable database
	 * @return Database 
	 */
	public Database open(){
		dbHelper = new DbHelper(ourContext);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	/**
	 * closes the database
	 */
	public void close(){
		dbHelper.close();
	}
	
/**
 * creates a user's parameters and enters the user in a table
 * @param loginStr
 * @param passwordStr
 * @param emailStr
 * @param personNameStr
 * @return long, you don't need to worry about
 */
	public long createUser(String loginStr, String passwordStr, String emailStr, String personNameStr){
		//makes a set of content values
		ContentValues cv = new ContentValues();
		//adds all of the user's information to the content value set
		cv.put(KEY_LOGIN, loginStr);
		cv.put(KEY_PASSWORD, passwordStr);
		cv.put(KEY_EMAIL, emailStr);
		cv.put(KEY_NAME, personNameStr);
		//inserts the content value set in the user table at the next available row
		Log.d("Debug", loginStr + passwordStr+" should hopefully be entered?");
		return database.insert(DATABASE_USERTABLE, null, cv);	
	}
	/**
	 * checks to see the result of a log in attempt
	 * @param login
	 * @param password
	 * @return int with the following results
	 * 		1 = successful
	 * 		0 = account exists, password wrong
	 * 		-1 =  error or account doesn't exist
	 */
	public int checkUser(String login, String password){
		String[] cols = new String[]{KEY_LOGIN, KEY_PASSWORD, KEY_EMAIL, KEY_NAME};
		int status = -1;
		try{
			//creates a cursor that can traverse an area that contains the columns specified
			//in cols and that selects the row/s  where the key_login = login
			Cursor c = database.query(DATABASE_USERTABLE, cols, KEY_LOGIN + "='" + login + "'", null, null, null, null);
			Log.d("DEBUG", "found user");
			//if there is a result where the user exists 
			if(c!=null){
				//goes to the first row of the area that is highlighted in cursor c
				c.moveToFirst();
				//stores the checked password
				String checkPassword = c.getString(1);
				Log.d("DEBUG", "Entered password: " + password + ", " + "Stored password: " + checkPassword);
				//checks to see if the password is correct
				if(checkPassword.equals(password)){
					//if it is, assign the currentUser to a new User with that row's 
					currentUser = new UserAccount(c.getString(0), c.getString(1), c.getString(2), c.getString(3));
					status = 1;
				}
				//else the user exists and the password is wrong
				else{
					status = 0;
				}
			}
			//but otherwise...there's no matching user
			else{
				status = -1;
			}
		}
		//catches ugly exceptions and prints the error
		catch(Exception e){
				Log.d("DEBUG", e.toString());
				status = -1;
		}
		return status;
	}
	/**
	 * user getter
	 * @return UserAccount current user
	 */
	public UserAccount getUser(){
		return currentUser;
	}
	/**
	 * returns the list of to do items for the given user
	 * @param user
	 * @return ArrayList<ToDoItem>, the list of to do items of the given user
	 */
	public ArrayList<ToDoItem> getItems(String user){
		//creates a set of columns that hold the instance variables of any to do item
		String [] cols = new String[]{KEY_ITEMNAME, KEY_USER, KEY_DETAILS, KEY_CATEGORY, KEY_DUEDATE,
				KEY_DUETIME, KEY_LOCATION, KEY_ISCHECKED, KEY_ROWID};
		//creates a cursor that can traverse the highlighted area that covers all of the specified columns
		//in cols and any rows where the string user is equal the to the user string passed in
		Cursor c = database.query(DATABASE_TODOITEMTABLE, cols, KEY_USER + "='" + user + "'", null, null, null, null);
		//creates an array list of to do items
		ArrayList<ToDoItem> result = new ArrayList<ToDoItem>();
		//moves cursor from the first row (in the highlighted cursor area) to the
		//next row until the cursor passes the last row in the highlighted area. 
		for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
			//converts the ischecked value from a string to a boolean
			boolean ischecked = false;
			if(c.getString(7).equals("true")){
				ischecked = true;
			}
			//creates/adds to do items to the array list 
			result.add(new ToDoItem(c.getString(0), c.getString(1), c.getString(2), c.getString(3), c.getString(4),
					c.getString(5), c.getString(6), ischecked, c.getString(8)));
		}
		//returns the array list
		return result;
	}
	public ArrayList<String> getLocations(String user){
		//creates a set of columns that hold the instance variables of any to do item
		ArrayList<ToDoItem> items = getItems(user);
		ArrayList<String> locs = new ArrayList<String>();
		for(ToDoItem i: items){
			locs.add(i.getLocation());
		}
		//returns the array list
		return locs;
	}
	/**
	 * 
	 * @param user			The user whose items to get
	 * @param category		Category to filter by; null returns all categories
	 * @param isChecked		Items state to filter by; null returns all
	 * @param orderBy		Order items by this
	 * @param orderByArg	Date, time, or location to order by
	 * @return
	 */
	public ArrayList<ToDoItem> getFilteredItems(String user, String category, String isChecked, String orderByThis, String orderByArg){
		String [] cols = new String[]{KEY_ITEMNAME, KEY_USER, KEY_DETAILS, KEY_CATEGORY, KEY_DUEDATE,
				KEY_DUETIME, KEY_LOCATION, KEY_ISCHECKED, KEY_ROWID};
		
		// Set up filters
		String filterBy = "";
		if(category!=null && !category.equals("null")) {
			filterBy+=" AND " + KEY_CATEGORY + "='" + category + "'";
		}
		if(isChecked!=null && !isChecked.equals("null")) {
			filterBy+=" AND " + KEY_ISCHECKED + "='" + isChecked + "'";
		}
		
		// Set up ordering
		String orderBy = null;
		if(orderByThis!=null) {
			if(orderByThis.equalsIgnoreCase("duedate")) {
				filterBy+=" AND " + KEY_DUEDATE + ">='" + orderByArg + "'";
				orderBy = KEY_DUEDATE;
			}
		}
		
		Cursor c = database.query(DATABASE_TODOITEMTABLE, cols, KEY_USER + "='" + user  + "'" + filterBy, null, null, null, orderBy);
		ArrayList<ToDoItem> result = new ArrayList<ToDoItem>();
		for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
			boolean ischecked = false;
			if(c.getString(7).equals("true")){
				ischecked = true;
			}
			result.add(new ToDoItem(c.getString(0), c.getString(1), c.getString(2), c.getString(3), c.getString(4),
					c.getString(5), c.getString(6), ischecked, c.getString(8)));
		}
		return result;
	}
	/**
	 * adds a new item to the database
	 * @param anItem
	 */
	public void addItem(ToDoItem anItem) {
		//creates a set of content values
		ContentValues cv = new ContentValues();
		//adds instances of any to do item to the content value set
		cv.put(KEY_ITEMNAME, anItem.getName());
		cv.put(KEY_USER, anItem.getUser());
		cv.put(KEY_DETAILS, anItem.getDetails());
		cv.put(KEY_CATEGORY, anItem.getCategory().getName());
		cv.put(KEY_DUEDATE, anItem.getDueDate());
		cv.put(KEY_DUETIME, anItem.getDueTime());
		cv.put(KEY_LOCATION, anItem.getLocation());
		//converts the ischecked parameter into a boolean
		String ischecked = "false";
		if(anItem.isChecked()){
			ischecked = "true";
		}
		cv.put(KEY_ISCHECKED, ischecked);
		//adds the to do item parameters to a row in the to do item table
		database.insert(DATABASE_TODOITEMTABLE, null, cv);
		
	}
	
	/**
	 * updates an item in the table
	 * @param anItem
	 */
	public void updateItem(ToDoItem anItem) {
		//creates a set of content values containing the parameters of the item
		ContentValues cv = new ContentValues();
		cv.put(KEY_ITEMNAME, anItem.getName());
		cv.put(KEY_USER, anItem.getUser());
		cv.put(KEY_DETAILS, anItem.getDetails());
		cv.put(KEY_CATEGORY, anItem.getCategory().getName());
		cv.put(KEY_DUEDATE, anItem.getDueDate());
		cv.put(KEY_DUETIME, anItem.getDueTime());
		cv.put(KEY_LOCATION, anItem.getLocation());
		String ischecked = "false";
		if(anItem.isChecked()){
			ischecked = "true";
		}
		cv.put(KEY_ISCHECKED, ischecked);
		//only difference between this and addItem is the updating part...
		database.update(DATABASE_TODOITEMTABLE, cv, KEY_ROWID + "='" + anItem.getID() + "'", null);
	}
	
	/**
	 * Adds or updates a setting given a setting name and value
	 * @param settingName
	 * @param settingValue
	 */
	public void setSetting(String settingName, String settingValue){
		ContentValues cv = new ContentValues();
		cv.put(KEY_VALUE, settingValue);
		if(database.update(DATABASE_SETTINGS, cv, KEY_SETTING + "='" + settingName.toLowerCase() + "'", null) == 0) {
			cv = new ContentValues();
			cv.put(KEY_SETTING, settingName.toLowerCase());
			cv.put(KEY_VALUE, settingValue);
			database.insert(DATABASE_SETTINGS, null, cv);
		}
	}
	
	/**
	 * Gets a setting value given the setting name
	 * @param settingName
	 * @return
	 */
	public String getSetting(String settingName) {
		String value = null;
		String [] cols = new String[]{KEY_VALUE};
		Cursor c = database.query(DATABASE_SETTINGS, cols, KEY_SETTING + "='" + settingName.toLowerCase() + "'",
				null, null, null, null);
		c.moveToFirst();
		if(c.getCount() != 0) {
			value = c.getString(0);
		}
		return value;
	}
	public void deletebyID(String id){
		database.delete(DATABASE_USERTABLE, KEY_LOGIN + "='" + id + "'", null);
	}

	public void deleteChecked() {
		database.delete(DATABASE_TODOITEMTABLE, KEY_USER + "='" + currentUser.getID() + "' AND "+ KEY_ISCHECKED + "='true'", null);
		
	}

}
